# -*- encoding=utf-8 -*-
import os
import sqlite3

from WebCenter.settings import BASE_DIR

'''
by：shenhuawade
qq: 317909531
功能：保存数据库内容，一个自增ID字段，3个keyWord,title,hasPost字段，用于效率查找

code/time:2020.2.9
'''

class dbClass_toutiao():

    def opendb(self):
        dbpath = os.path.join(BASE_DIR, 'static\\toutiao.db')
        conn = sqlite3.connect(dbpath,check_same_thread=False)
        cursor = conn.cursor()
        return conn, cursor


    def closedb(self, cursor, conn):
        cursor.commit()
        conn.close()


    '''# 创建表 keyWord,title,hasPost'''
    def initdb(self, cursor):
        create_table_sql = '''create table IF NOT EXISTS happy(
                            'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
                            'keyWord' text NOT NULL,
                            'title' text unique NOT NULL,
                            'hasPost' INTEGER NOT NULL)'''
        cursor.execute(create_table_sql)  # 执行这个语句
        '''创建一个复用标题库，发过的标题都放这里'''
        create_table_sql = '''create table IF NOT EXISTS reuse(
                                    'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
                                    'keyWord' text NOT NULL,
                                    'title' text unique NOT NULL,
                                    'hasPost' INTEGER NOT NULL)'''
        cursor.execute(create_table_sql)  # 执行这个语句


    '''增，happy表'''
    def insertdb(self, cursor, keyWord, title, hasPost):
        cursor.execute(f" insert or ignore into happy (keyWord,title,hasPost) values ('{keyWord}','{title}','{hasPost}')")
        cursor.commit()
    '''增，reuse 表'''
    def insertdb_reuse(self, cursor, keyWord, title, hasPost):
        cursor.execute(f" insert or ignore into reuse (keyWord,title,hasPost) values ('{keyWord}','{title}','{hasPost}')")
        cursor.commit()

    '''查表内的所有数据'''
    def searchdb(self, cursor):
        search_sql = "select ID,keyWord,title,hasPost from happy"
        results = cursor.execute(search_sql)
        all_results = results.fetchall()
        # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
        return all_results
    '''查表内的所有数据，排除已发送的 hasPost = 1标题已发送和垃圾库 0没有发的标题 2复用库'''
    def searchdbPostStyle(self, cursor,postNUM):
        search_sql = f"select ID,keyWord,title,hasPost from happy where hasPost={postNUM}"
        results = cursor.execute(search_sql)
        all_results = results.fetchall()
        # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
        return all_results


    '''查询---happy--输入关键词keyWord，返回标题和ID
    
    '''
    def getID_By_keyWord(self, cursor, keyWord):
        result = cursor.execute(f"SELECT ID,title From happy WHERE keyWord = '{keyWord}' and hasPost = 0")
        if result.fetchall()==[]:
            print("库中没有这首歌")
            return 'error','库中没有这首歌'
        else:
            resultList = tuple(result.fetchall())
            ID = resultList[0][0]
            title = resultList[0][1]
            return ID,title

    '''查询---reuse--输入关键词title，返回是否存在,只需要返回有和没有，true flase
    '''
    def getID_By_keyWordFromReuse(self, cursor, title):
        result = cursor.execute(f"SELECT ID,title From reuse WHERE title = '{title}'")
        if result.fetchall() == []:
            print("库中没有这首歌")
            return False
        else:
            resultList = tuple(result.fetchall())

            return True

    '''改---把输入id，修改该行内容'''
    def setDbValueTo1(self,cursor,ID):
        cursor.execute(f"UPDATE happy SET hasPost = 1  WHERE  ID = '{ID}'")
        cursor.commit()
    '''改---把输入id,和title，更新标题内容'''
    def setDbTitleValue(self,cursor,ID,title):
        cursor.execute(f"UPDATE happy SET title = '{title}'  WHERE  ID = '{ID}'")
        cursor.commit()

    def setDbValueTo2(self,cursor,ID):
        cursor.execute(f"UPDATE happy SET hasPost = 2  WHERE  ID = '{ID}'")
        cursor.commit()

    '''删除--删除某个id下的行'''
    def deleteID(self,cursor,ID):
        cursor.execute(f'DELETE FROM happy WHERE ID = "{ID}"')
        cursor.commit()

    '''改---替换功能，把title列中，敏感词替换成空'''
    #替换思路，根据现有的句子，把里边还有关键词的列都选出来，然后尝试更新替换，如果失败，就删除这一行
    def setDbValueTiHuan(self,cursor,deleteword):
        result = cursor.execute(f"select * from happy where title like '%{deleteword}%'")
        # print(tuple(result.fetchall()))
        resultList = tuple(result.fetchall())
        for resultitem in resultList:
            ID = resultitem[0]
            print(ID)
            title = resultitem[2]
            newtitle =title.replace(deleteword,'')
            print(newtitle)
            try:
                cursor.execute(f"UPDATE happy SET title = '{newtitle}'  WHERE  ID = '{ID}'")
                cursor.commit()
                print('更新完成')
            except Exception:  # 方法一：捕获所有异常
                #     # 如果发生异常，则回滚
                print("发生异常", Exception)
                print(f'删除重复行{ID}')
                cursor.execute(f'DELETE FROM happy WHERE ID = "{ID}"')
                cursor.commit()
    def deletSong(self,cursor,keyWord):
        try:
            cursor.execute(f'DELETE FROM happy WHERE keyWord = "{keyWord}"')
            cursor.commit()
        except Exception:  # 方法一：捕获所有异常
            print('删除失败')

        # try:
        #     result = cursor.execute(f"update happy set title =replace(title,'{deleteword}','') ")
        #     cursor.commit()
        # except Exception:  # 方法一：捕获所有异常
        #     # 如果发生异常，则回滚
        #     print("发生异常", Exception)
        #     # cursor.rollback()

def deleteKeywordNotInTitle(cursor,dbClass,conn):
    #删除title中没有《歌名》这种行
    result = dbClass.searchdb(cursor)

    for row in result:
        jsondata = {}
        # print(row)
        jsondata['id'] = list(row)[0]
        jsondata['songname'] = list(row)[1]
        jsondata['title'] = list(row)[2]
        jsondata['haspost'] = list(row)[3]
        if f'《{list(row)[1]}》' in list(row)[2]:   #如果title包含有歌名，就过滤，不包含就删除
            pass
        else:
            dbClass.deleteID(cursor,list(row)[0])


def deleteTitlewordTooLittle(cursor,dbClass,conn):  #删除标题长度不符合的内容
    result = dbClass.searchdb(cursor)

    for row in result:
        jsondata = {}
        # print(row)
        jsondata['id'] = list(row)[0]
        jsondata['songname'] = list(row)[1]
        jsondata['title'] = list(row)[2]
        jsondata['haspost'] = list(row)[3]

        if len(list(row)[2]) >= 16 and len(list(row)[2]) < 40: #符合长度，保留，否则删除
            print(list(row)[2])
            print(len(list(row)[2]))
            pass
        else:
            dbClass.deleteID(cursor,list(row)[0])



if __name__ == '__main__':
    dbClass = dbClass_toutiao()
    cursor, conn = dbClass.opendb()
    dbClass.initdb(cursor)
    #
    # titleRowid = dbClass.searchRowId(cursor=cursor, title='2003年电影《下一站天后》主题曲《下一站天后》影视原声-Twins')
    # print(titleRowid)
    # deleteTitlewordTooLittle(cursor,dbClass,conn)
    '''功能2【增】 ，插入数据库内容，字段有cursor, keyWord, title, hasPost'''
    # dbClass.insertdb(cursor, 'ni2hao', '12003年11电影《下一站天后》主题曲《下一站天后》影视原声-Twins',0)


    '''功能1【查】，给一首歌名(keyWord)，返回id和标题)'''
    result = dbClass.searchdbPostStyle(cursor,2)
    print(result)


    '''功能3【改】，根据id，修改该列的字段hasPost 为1'''
    # dbClass.setDbValue(cursor, 19)

    dbClass.closedb(cursor, conn)


